package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.User;
import com.LostAndFound.utils.DataSourceUtils;

public class UserListDao {

	public int getTotalUserForEasyUI() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from user";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}
	// 分页，得到user的记录列表
	public List<User> getUserListForEasyUI(int startIndex, int pageSize) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from user limit ?,?";
		return runner.query(sql, new BeanListHandler<User>(User.class),startIndex,pageSize);
	}
	// 删除用户所需删除的article和foundArticle中的记录
	public int getTotalToDelete(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article where foundflag = 0 and uid = ?";
		Long total = (Long) runner.query(sql, new ScalarHandler(), uid);
		return total.intValue(); 
	}
	public Object selectAidFromArticleToDelete(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select aid from article where uid = ?";
		return runner.query(sql, new ScalarHandler("aid"),uid);
	}
	public void deleteFromFoundArticle(Object obj) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from foundarticle where aid = ?";
		runner.update(sql, obj);
	}
	public void deleteFromArticle(Object obj) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where aid = ?";
		runner.update(sql, obj);
	}
	public void deleteFromArticleForInvalid(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where uid = ? and invalidflag = 0";
		runner.update(sql, uid);
	}
	public void deleteFromArticleForNotFoundAndValid(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where foundflag = 1 and invalidflag = 1 and uid = ?";
		runner.update(sql,uid);
	}
	public void deleteFromArticleForUser(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from user where uid = ?";
		runner.update(sql, uid);
	}
	public int checkAddUsername(String username) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from user where username = ?";
		Long row = (Long) runner.query(sql, new ScalarHandler(),username);
		return row.intValue();
	}
	public void handleAddUserEasyUI(User user) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into user values(?,?,?,?,?,?,?,?)";
		Object[] params = {user.getUid(),user.getUsername(),user.getPassword(),user.getGender(),user.getCellphone(),
				user.getEmail(),user.getRealname(),user.getResume()};
		runner.update(sql, params);
	}
	public void handleEditUserEasyUI(User user) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set password=?,gender=?,cellphone=?,email=?,realname=?,resume=? "
				+ " where uid = ?";
		Object[] params = {user.getPassword(),user.getGender(),user.getCellphone(),
				user.getEmail(),user.getRealname(),user.getResume(),user.getUid()};
		runner.update(sql,params);
	}


}
